<?php
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel.php");
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel/Writer/Excel2007.php");

class Silk_Zgb_Block_Report_Item extends Mage_Core_Block_Template
{
    public function getTeamCustomer()
    {
        $collection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToSelect('entity_id')
            ->addAttributeToFilter('group_id', array('eq'=>4));

        $ids = array(0);
        foreach($collection as $customer)
        {
            $ids[] = $customer->getId();
        }
        return $ids;
    }

    public function getItemCollection()
    {
        $startDate = $this->getRequest()->getParam('start_date');
        $endDate = $this->getRequest()->getParam('end_date');
        $websiteId = $this->getRequest()->getParam('website_id');
        $shopName = $this->getRequest()->getParam('shop_name');
        $status = $this->getRequest()->getParam('status');
        $is_salt = $this->getRequest()->getParam('is_salt');

        $export = $this->getRequest()->getParam('export');
        $apply = $this->getRequest()->getParam('apply');

        $session = Mage::getSingleton('admin/session');
        
        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
        }
        
        $ids = array();
        $storeIds = array();

        $teams = implode(',',$this->getTeamCustomer());


        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');

        //$sql = "SELECT o.`created_at`,o.`increment_id`,o.`subtotal`,o.status,dw.dealer_name,cev.`value` AS shop_name FROM `sales_flat_order` o
        //        LEFT JOIN (SELECT wh.id warehouse_id,d.id AS dealer_id,d.name AS dealer_name FROM zgb_warehouse wh LEFT JOIN zgb_dealer d ON wh.dealer_id=d.id) dw ON o.`warehouse_id`=dw.warehouse_id
        //        LEFT JOIN `customer_entity_varchar` cev ON o.`customer_id`=cev.`entity_id` AND cev.`attribute_id`=7
        //        WHERE 1=1 and o.customer_id not in({$teams})";

        $sql = "SELECT o.dealer as dealer_name,o.website_name,o.created_at,o.increment_id,c.store_name,i.name,i.qty,i.unit,i.price,i.row_total,i.row_invoiced,o.grand_total_ex,o.status 
            FROM zgb_order AS o LEFT JOIN zgb_order_item AS i ON o.entity_id=i.order_id
            LEFT JOIN zgb_store AS c ON o.customer_id=c.entity_id where 1=1 and o.customer_id not in({$teams})";

        if($is_salt)
        {
            if($is_salt == 1)
            {
                $sql.= "and i.is_salt=0";
            }
            else
            {
                $sql.= "and i.is_salt=1";
            }
        }

        if($startDate)
        {
            $startDate = "{$startDate} 00:00:00";
            $sql .= " and o.created_at>= '{$startDate}'";
        }

        if($endDate)
        {
            $endDate = "{$endDate} 23:59:59";
            $sql .= " and o.created_at<= '{$endDate}'";
        }

        if($shopName)
        {
            $sql .= " and store_name LIKE '%{$shopName}%'";
        }

        if($websiteId)
        {
            $sql .= " and o.website_id='{$websiteId}'";
        }

        if($status)
        {
            if($status == 'complete')
            {
                $sql .= " and o.status in ('complete','complete_order')";
            }
            else
            {
                $sql .= " and o.status='{$status}'";
            }
        }

        $sql .= " order by o.increment_id desc ";

        //echo $sql;
        if($export || $apply)
        {
            $query = $db->query($sql);
            $items = $query->fetchAll();
        }
        else
        {
            $items = array();
        }

        return $items;
    }

    public function exportToExcel()
    {
        $items = $this->getItemCollection();
        $objPHPExcel  = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->setTitle("订单统计报表");
        $sheet->getColumnDimension('A')->setWidth(30);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(30);
        $sheet->getColumnDimension('E')->setWidth(50);
        $sheet->getColumnDimension('F')->setWidth(12);
        $sheet->getColumnDimension('G')->setWidth(12);
        $sheet->getColumnDimension('H')->setWidth(12);
        $sheet->getColumnDimension('I')->setWidth(12);
        $sheet->getColumnDimension('J')->setWidth(12);
        $sheet->getColumnDimension('K')->setWidth(12);
        $sheet->getColumnDimension('L')->setWidth(12);

        $sheet->getStyle('A1')->getFont()->setSize(13);
        $sheet->getStyle('A1')->getFont()->setBold(true);

        $sheet->getStyle('B1')->getFont()->setSize(13);
        $sheet->getStyle('B1')->getFont()->setBold(true);

        $sheet->getStyle('C1')->getFont()->setSize(13);
        $sheet->getStyle('C1')->getFont()->setBold(true);

        $sheet->getStyle('D1')->getFont()->setSize(13);
        $sheet->getStyle('D1')->getFont()->setBold(true);

        $sheet->getStyle('E1')->getFont()->setSize(13);
        $sheet->getStyle('E1')->getFont()->setBold(true);

        $sheet->getStyle('F1')->getFont()->setSize(13);
        $sheet->getStyle('F1')->getFont()->setBold(true);

        $sheet->getStyle('G1')->getFont()->setSize(13);
        $sheet->getStyle('G1')->getFont()->setBold(true);

        $sheet->getStyle('H1')->getFont()->setSize(13);
        $sheet->getStyle('H1')->getFont()->setBold(true);

        $sheet->getStyle('I1')->getFont()->setSize(13);
        $sheet->getStyle('I1')->getFont()->setBold(true);

        $sheet->getStyle('J1')->getFont()->setSize(13);
        $sheet->getStyle('J1')->getFont()->setBold(true);

        $sheet->getStyle('K1')->getFont()->setSize(13);
        $sheet->getStyle('K1')->getFont()->setBold(true);

        $sheet->getStyle('L1')->getFont()->setSize(13);
        $sheet->getStyle('L1')->getFont()->setBold(true);

        $sheet->getRowDimension('1')->setRowHeight(20);

        $styleThinBlackBorderOutline = array(
            'borders' => array (
               'outline' => array (
                  'style' => PHPExcel_Style_Border::BORDER_THIN, 
                  'color' => array ('argb' => 'FF000000'),
              ),
           ),
        );

        $statuses = array('pending'=>"待处理",'processing'=>"处理中",'complete'=>"已完成",'complete_delivering'=>"配送中",'complete_ship'=>"配送完成",'complete_order'=>"已完成",'canceled'=>"已取消",'closed'=>'已关闭');

        $row = 1;
        $sheet->setCellValue("A{$row}","站点");
        $sheet->setCellValue("B{$row}","订单日期");
        $sheet->setCellValue("C{$row}","订单编号");
        $sheet->setCellValue("D{$row}","店铺名");
        $sheet->setCellValue("E{$row}","商品名");
        $sheet->setCellValue("F{$row}","数量");
        $sheet->setCellValue("G{$row}","价格");
        $sheet->setCellValue("H{$row}","合计");
        $sheet->setCellValue("I{$row}","订单总计");
        $sheet->setCellValue("J{$row}","订单状态");
        $sheet->setCellValue("K{$row}","单位");
        $sheet->setCellValue("L{$row}","备注");

        $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("G{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("H{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("I{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("J{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("K{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("L{$row}")->applyFromArray($styleThinBlackBorderOutline);
        
        $row++;
        foreach($items as $item)
        {
            $sheet->setCellValue("A{$row}",$item['website_name']);
            $sheet->setCellValue("B{$row}",$item['created_at']);
            $sheet->setCellValue("C{$row}",$item['increment_id']);
            $sheet->setCellValue("D{$row}",$item['store_name']);
            $sheet->setCellValue("E{$row}",$item['name']);
            $sheet->setCellValue("F{$row}",intval($item['qty']));
            $sheet->setCellValue("G{$row}",$item['price']);
            $sheet->setCellValue("H{$row}",$item['row_total']);
            $sheet->setCellValue("I{$row}",$item['grand_total_ex']);
            $sheet->setCellValue("J{$row}",$statuses[$item['status']]);
            $sheet->setCellValue("K{$row}",$item['unit']);
            $sheet->setCellValue("L{$row}",'');
            

            $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("G{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("H{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("I{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("J{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("K{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("L{$row}")->applyFromArray($styleThinBlackBorderOutline);

            $row++;
        }
        //$sheet->setCellValue("F{$row}", "=SUM(F2:F".($row-1).")");
        //$sheet->setCellValue("G{$row}", "=SUM(G2:G".($row-1).")");
        return $objWriter;
    }

    public function convertDate($date)
    {
        $timestamp = strtotime($date);
        return date("Y年m月d日 H:i", $timestamp+3600*8);
    }


    public function getDealerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::DEALER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }

    public function getManufacturerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::MANUFACTURER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        echo $sql;
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }
}